<?php
/**
 * 物件リクエスト店舗検索用関数<br />
 *
 * @package apamanshop
 * @author　
 * @copyright　
 */
class Search_Model_Requesttenpo extends Search_Model_Base{

	/**
	* リクエスト店舗データ取得
	*
	* @access	public
	* @param	array	パラメータ
	* @return	array	リクエスト店舗データ
	*/
    public function getRequestTenpoSearch($params)
    {
		$data = array();
    	$db_params = array();

		// 検索区分が住所検索の場合
    	if($params['SrhKbn'] == Apm_Config_Search::SRHKBN_JYUSYO){
	    	// エリア周辺店舗
			$sql = $this->getJyusyoTenpoSearchAreaSql($params,$db_params);
			$data['area_tenpo'] = $this->dbGetList($sql,$db_params);

			// エリア周辺の物件を持つ店舗
			$sql = $this->getJyusyoTenpoSearchBukenSql($params,$data,$db_params);
			$data['buken_tenpo'] = $this->dbGetList($sql,$db_params);

		// 検索区分が沿線検索or路線図検索or住みたい駅検索
    	}else if($params['SrhKbn'] == Apm_Config_Search::SRHKBN_ENSENEKI || $params['SrhKbn'] == Apm_Config_Search::SRHKBN_ROSENZU
    			|| $params['SrhKbn'] == Apm_Config_Search::SRHKBN_SUMITAIEKI)
    	{
	    	// エリア周辺店舗
			$sql = $this->getEnsenTenpoSearchAreaSql($params,$db_params);
			$data['area_tenpo'] = $this->dbGetList($sql,$db_params);

			// エリア周辺の物件を持つ店舗
			$sql = $this->getEnsenTenpoSearchBukenSql($params,$data,$db_params);
			$data['buken_tenpo'] = $this->dbGetList($sql,$db_params);
    	}

		// 表示コンバート
		$cnt = count($data['area_tenpo']);
		for ($i=0; $i<$cnt; $i++) {
			$data['area_tenpo'][$i]['SHOP_DTL_URL'] = Apm_Bukkenurl::generateUrlByParametersShopDetail($data['area_tenpo'][$i]);
		}
		$cnt = count($data['buken_tenpo']);
		for ($i=0; $i<$cnt; $i++) {
			$data['buken_tenpo'][$i]['SHOP_DTL_URL'] = Apm_Bukkenurl::generateUrlByParametersShopDetail($data['buken_tenpo'][$i]);
		}

		return $data;
    }

	/**
	* 連想配列から指定キーの値を抜き出して配列で返す
	*
	* @access	public
	* @param	array	連想配列
	* @param	string	キー
	* @return	array
	*/
	public function arrayRemade($params,$key)
	{
		$remade = array();

		foreach ($params as $temp){
			$remade[] = $temp[$key];
		}

		return $remade;
	}

	/**
	* 都道府県・市区町村コードWhere句作成
	*
	* @access	public
	* @param	string	カラム名1
	* @param	string	カラム名2
	* @param	array
	* @return	string
	*/
	public function createWhereTown($colums1,$colums2,$params)
	{
		// コード数分ループ
		for($i=0;$i<count($params);$i++){

			if($i == 0){
				$sql = " (( ".$colums1." = '".substr($params[$i],0,2)."' AND ".$colums2." = '".substr($params[$i],2,3)."' )";
			}else{
				$sql.= " OR (".$colums1." = '".substr($params[$i],0,2)."' AND ".$colums2." = '".substr($params[$i],2,3)."' )";
			}
		}

		$sql .= ")";

		return $sql;
    }

	/**
	* ２パラメータからWhere句作成
	*
	* @access	public
	* @param	string	カラム名1
	* @param	string	カラム名2
	* @param	array	パラメータ1
	* @param	array	パラメータ2
	* @return	string
	*/
	public function createWhereTwoParam($colums1,$colums2,$params1,$params2)
	{
		// コード数分ループ
		for($i=0;$i<count($params1);$i++){

			// コード数分ループ
			for($j=0;$j<count($params2);$j++){
				if($j == 0){
					$sql = " (( ".$colums1." = '".$params1[$i]."' AND ".$colums2." = '".$params2[$j]."' )";
				}else{
					$sql.= " OR (".$colums1." = '".$params1[$i]."' AND ".$colums2." = '".$params2[$j]."' )";
				}
			}
		}

		$sql .= ")";

		return $sql;
    }

	/**
	* <> AND連結 Where句作成
	*
	* @access	public
	* @param	string	カラム名
	* @param	array	対象パラメータ
	* @return	string
	*/
	public function createWhereNotEq($colums,$params)
	{
		$where = $this->createWhere($colums,$params,"AND");

		$where = str_replace("=","<>",$where);

		return $where;
    }

    /**
	* 希望エリア周辺店舗取得SQL
	*
	* @access	public
	* @param	array
	* @param	array
	* @return	string
	*/
	public function getJyusyoTenpoSearchAreaSql($params, &$db_params)
	{
		$sql = "";
		$sql .= "SELECT";
		$sql .= "        DISTINCT M05_TENPO_CD";
		$sql .= "        ,M05_TENPO_MAIL";
		$sql .= "        ,M05_TENPO_NAME";
		$sql .= "        ,M05_TENPO_TODOUFUKEN_CD";
		$sql .= "        ,M05_TENPO_SHIKUCHOUSON_CD";
		$sql .= "        ,M05_JYUUSHO_TEXT";
		$sql .= "        ,M05_TENPO_BANCHI";
		$sql .= "        ,M05_TENPO_JYUUSHO_BIKOU";
		$sql .= "        ,M05_KOUTSUU_TEXT01";
		$sql .= "        ,M05_TOHO01";
		$sql .= "        ,M05_EIGYOU_ZIKAN_OPEN_H";
		$sql .= "        ,M05_EIGYOU_ZIKAN_OPEN_M";
		$sql .= "        ,M05_EIGYOU_ZIKAN_CLOSE_H";
		$sql .= "        ,M05_EIGYOU_ZIKAN_CLOSE_M";
		$sql .= "        ,M05_TEIKYUUBI";
		$sql .= "        ,M03_ENSEN_NAME";
		$sql .= "        ,M03_ENSEN_CD";
		$sql .= "        ,M03_EKI_CD";
		$sql .= "        ,M03_EKI_NAME";
		$sql .= "        ,A06_BUKKEN_CNT";
		$sql .= "        ,CEIL(DBMS_RANDOM.VALUE * 1000000) RANDOM";
		$sql .= "    FROM";
		$sql .= "        M05TENPO_INFO";
		$sql .= "        ,M03EKI";
		$sql .= "        ,A06TENPO_BUKKEN_COUNT";
		$sql .= "    WHERE";
		$sql .=          $this->createWhereTown("M05_TENPO_TODOUFUKEN_CD","M05_TENPO_SHIKUCHOUSON_CD",$params['CityCd'],"");
		$sql .= "        AND M05_KOUKAI_FLAG = 1";
		$sql .= "        AND M05_EKI_CD01 = M03_EKI_CD(+)";
		//$sql .= "        AND M05_TENPO_CD <> '03008604'";
		$sql .= "        AND (";
		$sql .= "            M05_TENPO_CD > '01000000'";
		$sql .= "            AND M05_TENPO_CD < '48000000'";
		$sql .= "        )";
		$sql .= $this->createWhereNotinShopList("M05_TENPO_CD", $db_params);
		$sql .= "        AND M05_TENPO_CD = A06_TENPO_CD";
		$sql .= "    ORDER BY";
		$sql .= "        RANDOM";

		return $sql;
	}


	/**
	* 希望エリア周辺物件を持つ店舗取得SQL
	*
	* @access	public
	* @param	array
	* @param	array
	* @return	string
	*/
	private function getJyusyoTenpoSearchBukenSql($params,$data, &$db_params)
	{

		$sql = "";
		$sql .= "SELECT";
		$sql .= "        CNT";
		$sql .= "        ,M05_TENPO_CD";
		$sql .= "        ,M05_TENPO_MAIL";
		$sql .= "        ,M05_TENPO_NAME";
		$sql .= "        ,M05_TENPO_TODOUFUKEN_CD";
		$sql .= "        ,M05_TENPO_SHIKUCHOUSON_CD";
		$sql .= "        ,M05_JYUUSHO_TEXT";
		$sql .= "        ,M05_TENPO_BANCHI";
		$sql .= "        ,M05_TENPO_JYUUSHO_BIKOU";
		$sql .= "        ,M05_KOUTSUU_TEXT01";
		$sql .= "        ,M05_TOHO01";
		$sql .= "        ,M05_EIGYOU_ZIKAN_OPEN_H";
		$sql .= "        ,M05_EIGYOU_ZIKAN_OPEN_M";
		$sql .= "        ,M05_EIGYOU_ZIKAN_CLOSE_H";
		$sql .= "        ,M05_EIGYOU_ZIKAN_CLOSE_M";
		$sql .= "        ,M05_TEIKYUUBI";
		$sql .= "        ,M05_EKI_CD01";
		$sql .= "        ,M03_ENSEN_NAME";
		$sql .= "        ,M03_ENSEN_CD";
		$sql .= "        ,M03_EKI_CD";
		$sql .= "        ,M03_EKI_NAME";
		$sql .= "        ,A06_BUKKEN_CNT";
		$sql .= "        ,CEIL(DBMS_RANDOM.VALUE * 1000000) RANDOM";
		$sql .= "    FROM";
		$sql .= "        M03EKI";
		$sql .= "        ,A06TENPO_BUKKEN_COUNT";
		$sql .= "        ,(";
		$sql .= "            SELECT";
		$sql .= "                    COUNT(T03.T03_HEYA_CD) AS CNT";
		$sql .= "                    ,M05.M05_TENPO_CD";
		$sql .= "                    ,M05.M05_TENPO_MAIL";
		$sql .= "                    ,M05.M05_TENPO_NAME";
		$sql .= "                    ,M05.M05_TENPO_TODOUFUKEN_CD";
		$sql .= "                    ,M05.M05_TENPO_SHIKUCHOUSON_CD";
		$sql .= "                    ,M05.M05_JYUUSHO_TEXT";
		$sql .= "                    ,M05.M05_TENPO_BANCHI";
		$sql .= "                    ,M05.M05_TENPO_JYUUSHO_BIKOU";
		$sql .= "                    ,M05.M05_KOUTSUU_TEXT01";
		$sql .= "                    ,M05.M05_TOHO01";
		$sql .= "                    ,M05.M05_EIGYOU_ZIKAN_OPEN_H";
		$sql .= "                    ,M05.M05_EIGYOU_ZIKAN_OPEN_M";
		$sql .= "                    ,M05.M05_EIGYOU_ZIKAN_CLOSE_H";
		$sql .= "                    ,M05.M05_EIGYOU_ZIKAN_CLOSE_M";
		$sql .= "                    ,M05.M05_TEIKYUUBI";
		$sql .= "                    ,M05.M05_EKI_CD01";
		$sql .= "                FROM";
		$sql .= "                    T03WEB_HEYA T03";
		$sql .= "                    ,M05TENPO_INFO M05";
		$sql .= "                WHERE";
		$sql .=                      $this->createWhereTown("T03_TODOUFUKEN_CD","T03_SHIKUCHOUSON_CD",$params['CityCd'],"");
		if(! empty($data["area_tenpo"])){
			$sql .= "                AND ".$this->createWhereNotEq("M05_TENPO_CD",$this->arrayRemade($data["area_tenpo"],"M05_TENPO_CD"));
		}
		$sql .= "                    AND T03.T03_TENPO_CD = M05.M05_TENPO_CD";
		$sql .= "                    AND M05.M05_KOUKAI_FLAG = 1";
		//$sql .= "                    AND M05.M05_TENPO_CD <> '03008604'";
		$sql .= "                    AND (";
		$sql .= "                        M05.M05_TENPO_CD > '01000000'";
		$sql .= "                        AND M05.M05_TENPO_CD < '48000000'";
		$sql .= "                    )";
		$sql .= $this->createWhereNotinShopList("M05_TENPO_CD", $db_params);
		$sql .= "                    AND M05.M05_TENPO_TODOUFUKEN IS NOT NULL";
		$sql .= "                    AND M05.M05_TENPO_SHIKUCHOUSON IS NOT NULL";
		$sql .= "                GROUP BY";
		$sql .= "                    M05_TENPO_CD";
		$sql .= "                    ,M05_TENPO_MAIL";
		$sql .= "                    ,M05_TENPO_NAME";
		$sql .= "                    ,M05_TENPO_TODOUFUKEN_CD";
		$sql .= "                    ,M05_TENPO_SHIKUCHOUSON_CD";
		$sql .= "                    ,M05_JYUUSHO_TEXT";
		$sql .= "                    ,M05_TENPO_BANCHI";
		$sql .= "                    ,M05_TENPO_JYUUSHO_BIKOU";
		$sql .= "                    ,M05_KOUTSUU_TEXT01";
		$sql .= "                    ,M05_TOHO01";
		$sql .= "                    ,M05_EIGYOU_ZIKAN_OPEN_H";
		$sql .= "                    ,M05_EIGYOU_ZIKAN_OPEN_M";
		$sql .= "                    ,M05_EIGYOU_ZIKAN_CLOSE_H";
		$sql .= "                    ,M05_EIGYOU_ZIKAN_CLOSE_M";
		$sql .= "                    ,M05_TEIKYUUBI";
		$sql .= "                    ,M05_EKI_CD01";
		$sql .= "        )";
		$sql .= "    WHERE";
		$sql .= "        M05_EKI_CD01 = M03_EKI_CD(+)";
		$sql .= "        AND M05_TENPO_CD = A06_TENPO_CD";
		$sql .= "    ORDER BY";
		$sql .= "        RANDOM";

		return $sql;
	}

	/**
	* 希望駅周辺店舗取得SQL
	*
	* @access	public
	* @param	array
	* @param	array
	* @return	string
	*/
	private function getEnsenTenpoSearchAreaSql($params, &$db_params)
	{
		$sql = "";
		$sql .= "SELECT";
		$sql .= "        M05_TENPO_CD";
		$sql .= "        ,M05_TENPO_MAIL";
		$sql .= "        ,M05_TENPO_NAME";
		$sql .= "        ,M05_TENPO_TODOUFUKEN_CD";
		$sql .= "        ,M05_TENPO_SHIKUCHOUSON_CD";
		$sql .= "        ,M05_JYUUSHO_TEXT";
		$sql .= "        ,M05_TENPO_BANCHI";
		$sql .= "        ,M05_TENPO_JYUUSHO_BIKOU";
		$sql .= "        ,M05_KOUTSUU_TEXT01";
		$sql .= "        ,M05_TOHO01";
		$sql .= "        ,M05_EIGYOU_ZIKAN_OPEN_H";
		$sql .= "        ,M05_EIGYOU_ZIKAN_OPEN_M";
		$sql .= "        ,M05_EIGYOU_ZIKAN_CLOSE_H";
		$sql .= "        ,M05_EIGYOU_ZIKAN_CLOSE_M";
		$sql .= "        ,M05_TEIKYUUBI";
		$sql .= "        ,M03_ENSEN_NAME";
		$sql .= "        ,M03_ENSEN_CD";
		$sql .= "        ,M03_EKI_CD";
		$sql .= "        ,M03_EKI_NAME";
		$sql .= "        ,A06_BUKKEN_CNT";
		$sql .= "        ,CEIL(DBMS_RANDOM.VALUE * 1000000) RANDOM";
		$sql .= "    FROM";
		$sql .= "        (";
		$sql .= "            SELECT";
		$sql .= "                    M05_TENPO_CD";
		$sql .= "                    ,M05_TENPO_MAIL";
		$sql .= "                    ,M05_TENPO_NAME";
		$sql .= "                    ,M05_TENPO_TODOUFUKEN_CD";
		$sql .= "                    ,M05_TENPO_SHIKUCHOUSON_CD";
		$sql .= "                    ,M05_JYUUSHO_TEXT";
		$sql .= "                    ,M05_TENPO_BANCHI";
		$sql .= "                    ,M05_TENPO_JYUUSHO_BIKOU";
		$sql .= "                    ,M05_KOUTSUU_TEXT01";
		$sql .= "                    ,M05_TOHO01";
		$sql .= "                    ,M05_EIGYOU_ZIKAN_OPEN_H";
		$sql .= "                    ,M05_EIGYOU_ZIKAN_OPEN_M";
		$sql .= "                    ,M05_EIGYOU_ZIKAN_CLOSE_H";
		$sql .= "                    ,M05_EIGYOU_ZIKAN_CLOSE_M";
		$sql .= "                    ,M05_TEIKYUUBI";
		$sql .= "                    ,M03_ENSEN_NAME";
		$sql .= "                    ,M03_ENSEN_CD";
		$sql .= "                    ,M03_EKI_CD";
		$sql .= "                    ,M03_EKI_NAME";
		$sql .= "                    ,A06_BUKKEN_CNT";
		$sql .= "                FROM";
		$sql .= "                    M05TENPO_INFO";
		$sql .= "                    ,M03EKI";
		$sql .= "                    ,A06TENPO_BUKKEN_COUNT";
		$sql .= "                    ,(";
		$sql .= "                        SELECT";
		$sql .= "                                T63_RENRAKU_CD AS TGT_RENRAKU_CD";
		$sql .= "                            FROM";
		$sql .= "                                T63RENRAKU_EKI_SUM";
		$sql .= "                            WHERE";
		$sql .=                                  $this->createWhere("T63_EKI_CD",$params["StCd"]);
		$sql .= "                                AND T63_DATA_KUBUN = 0";
		$sql .= "                            GROUP BY";
		$sql .= "                                T63_RENRAKU_CD";
		$sql .= "                    )";
		$sql .= "                WHERE";
		$sql .= "                    M03_RENRAKU_EKI_CD = TGT_RENRAKU_CD";
		$sql .= "                    AND M03_RENRAKU_EKI_CD IS NOT NULL";
		$sql .= "                    AND M05_EKI_CD01 = M03_EKI_CD(+)";
		$sql .= "                    AND M05_KOUKAI_FLAG = 1";
		//$sql .= "                    AND M05_TENPO_CD <> '03008604'";
		$sql .= "                    AND (";
		$sql .= "                        M05_TENPO_CD > '01000000'";
		$sql .= "                        AND M05_TENPO_CD < '48000000'";
		$sql .= "                    )";
		$sql .= $this->createWhereNotinShopList("M05_TENPO_CD", $db_params);
		$sql .= "                    AND M05_TENPO_CD = A06_TENPO_CD";
		$sql .= "                    AND ".$this->createWhere("M05_TENPO_TODOUFUKEN_CD",$params["PrefCd"]);
		$sql .= "                GROUP BY";
		$sql .= "                    M05_TENPO_CD";
		$sql .= "                    ,M05_TENPO_MAIL";
		$sql .= "                    ,M05_TENPO_NAME";
		$sql .= "                    ,M05_TENPO_TODOUFUKEN_CD";
		$sql .= "                    ,M05_TENPO_SHIKUCHOUSON_CD";
		$sql .= "                    ,M05_JYUUSHO_TEXT";
		$sql .= "                    ,M05_TENPO_BANCHI";
		$sql .= "                    ,M05_TENPO_JYUUSHO_BIKOU";
		$sql .= "                    ,M05_KOUTSUU_TEXT01";
		$sql .= "                    ,M05_TOHO01";
		$sql .= "                    ,M05_EIGYOU_ZIKAN_OPEN_H";
		$sql .= "                    ,M05_EIGYOU_ZIKAN_OPEN_M";
		$sql .= "                    ,M05_EIGYOU_ZIKAN_CLOSE_H";
		$sql .= "                    ,M05_EIGYOU_ZIKAN_CLOSE_M";
		$sql .= "                    ,M05_TEIKYUUBI";
		$sql .= "                    ,M03_ENSEN_NAME";
		$sql .= "                    ,M03_ENSEN_CD";
		$sql .= "                    ,M03_EKI_CD";
		$sql .= "                    ,M03_EKI_NAME";
		$sql .= "                    ,A06_BUKKEN_CNT";
		$sql .= "            UNION ALL";
		$sql .= "            SELECT";
		$sql .= "                    M05_TENPO_CD";
		$sql .= "                    ,M05_TENPO_MAIL";
		$sql .= "                    ,M05_TENPO_NAME";
		$sql .= "                    ,M05_TENPO_TODOUFUKEN_CD";
		$sql .= "                    ,M05_TENPO_SHIKUCHOUSON_CD";
		$sql .= "                    ,M05_JYUUSHO_TEXT";
		$sql .= "                    ,M05_TENPO_BANCHI";
		$sql .= "                    ,M05_TENPO_JYUUSHO_BIKOU";
		$sql .= "                    ,M05_KOUTSUU_TEXT01";
		$sql .= "                    ,M05_TOHO01";
		$sql .= "                    ,M05_EIGYOU_ZIKAN_OPEN_H";
		$sql .= "                    ,M05_EIGYOU_ZIKAN_OPEN_M";
		$sql .= "                    ,M05_EIGYOU_ZIKAN_CLOSE_H";
		$sql .= "                    ,M05_EIGYOU_ZIKAN_CLOSE_M";
		$sql .= "                    ,M05_TEIKYUUBI";
		$sql .= "                    ,M03_ENSEN_NAME";
		$sql .= "                    ,M03_ENSEN_CD";
		$sql .= "                    ,M03_EKI_CD";
		$sql .= "                    ,M03_EKI_NAME";
		$sql .= "                    ,A06_BUKKEN_CNT";
		$sql .= "                FROM";
		$sql .= "                    M05TENPO_INFO";
		$sql .= "                    ,M03EKI";
		$sql .= "                    ,A06TENPO_BUKKEN_COUNT";
		$sql .= "                WHERE ";
		$sql .=                      $this->createWhereTwoParam("M05_TENPO_TODOUFUKEN_CD","M05_EKI_CD01",$params['PrefCd'],$params['StCd']);
		$sql .= "                    AND M03_RENRAKU_EKI_CD IS NULL";
		$sql .= "                    AND M05_EKI_CD01 = M03_EKI_CD(+)";
		$sql .= "                    AND M05_KOUKAI_FLAG = 1";
		//$sql .= "                    AND M05_TENPO_CD <> '03008604'";
		$sql .= "                    AND (";
		$sql .= "                        M05_TENPO_CD > '01000000'";
		$sql .= "                        AND M05_TENPO_CD < '48000000'";
		$sql .= "                    )";
		$sql .= $this->createWhereNotinShopList("M05_TENPO_CD", $db_params);
		$sql .= "                    AND M05_TENPO_CD = A06_TENPO_CD";
		$sql .= "                GROUP BY";
		$sql .= "                    M05_TENPO_CD";
		$sql .= "                    ,M05_TENPO_MAIL";
		$sql .= "                    ,M05_TENPO_NAME";
		$sql .= "                    ,M05_TENPO_TODOUFUKEN_CD";
		$sql .= "                    ,M05_TENPO_SHIKUCHOUSON_CD";
		$sql .= "                    ,M05_JYUUSHO_TEXT";
		$sql .= "                    ,M05_TENPO_BANCHI";
		$sql .= "                    ,M05_TENPO_JYUUSHO_BIKOU";
		$sql .= "                    ,M05_KOUTSUU_TEXT01";
		$sql .= "                    ,M05_TOHO01";
		$sql .= "                    ,M05_EIGYOU_ZIKAN_OPEN_H";
		$sql .= "                    ,M05_EIGYOU_ZIKAN_OPEN_M";
		$sql .= "                    ,M05_EIGYOU_ZIKAN_CLOSE_H";
		$sql .= "                    ,M05_EIGYOU_ZIKAN_CLOSE_M";
		$sql .= "                    ,M05_TEIKYUUBI";
		$sql .= "                    ,M03_ENSEN_NAME";
		$sql .= "                    ,M03_ENSEN_CD";
		$sql .= "                    ,M03_EKI_CD";
		$sql .= "                    ,M03_EKI_NAME";
		$sql .= "                    ,A06_BUKKEN_CNT";
		$sql .= "        )";
		$sql .= "    GROUP BY";
		$sql .= "        M05_TENPO_CD";
		$sql .= "        ,M05_TENPO_MAIL";
		$sql .= "        ,M05_TENPO_NAME";
		$sql .= "        ,M05_TENPO_TODOUFUKEN_CD";
		$sql .= "        ,M05_TENPO_SHIKUCHOUSON_CD";
		$sql .= "        ,M05_JYUUSHO_TEXT";
		$sql .= "        ,M05_TENPO_BANCHI";
		$sql .= "        ,M05_TENPO_JYUUSHO_BIKOU";
		$sql .= "        ,M05_KOUTSUU_TEXT01";
		$sql .= "        ,M05_TOHO01";
		$sql .= "        ,M05_EIGYOU_ZIKAN_OPEN_H";
		$sql .= "        ,M05_EIGYOU_ZIKAN_OPEN_M";
		$sql .= "        ,M05_EIGYOU_ZIKAN_CLOSE_H";
		$sql .= "        ,M05_EIGYOU_ZIKAN_CLOSE_M";
		$sql .= "        ,M05_TEIKYUUBI";
		$sql .= "        ,M03_ENSEN_NAME";
		$sql .= "        ,M03_EKI_NAME";
		$sql .= "        ,M03_ENSEN_CD";
		$sql .= "        ,M03_EKI_CD";
		$sql .= "        ,A06_BUKKEN_CNT";
		$sql .= "    ORDER BY";
		$sql .= "        RANDOM";

		return $sql;
	}

	/**
	* 希望駅周辺物件を持つ店舗取得SQL
	*
	* @access	public
	* @param	array
	* @param	array
	* @return	string
	*/
	private function getEnsenTenpoSearchBukenSql($params,$data, &$db_params)
	{
		$sql .= "SELECT";
		$sql .= "        SUM(CNT) AS CNT";
		$sql .= "        ,M05_TENPO_CD";
		$sql .= "        ,M05_TENPO_MAIL";
		$sql .= "        ,M05_TENPO_NAME";
		$sql .= "        ,M05_TENPO_TODOUFUKEN_CD";
		$sql .= "        ,M05_TENPO_SHIKUCHOUSON_CD";
		$sql .= "        ,M05_JYUUSHO_TEXT";
		$sql .= "        ,M05_TENPO_BANCHI";
		$sql .= "        ,M05_TENPO_JYUUSHO_BIKOU";
		$sql .= "        ,M05_KOUTSUU_TEXT01";
		$sql .= "        ,M05_TOHO01";
		$sql .= "        ,M05_EIGYOU_ZIKAN_OPEN_H";
		$sql .= "        ,M05_EIGYOU_ZIKAN_OPEN_M";
		$sql .= "        ,M05_EIGYOU_ZIKAN_CLOSE_H";
		$sql .= "        ,M05_EIGYOU_ZIKAN_CLOSE_M";
		$sql .= "        ,M05_TEIKYUUBI";
		$sql .= "        ,M03_ENSEN_NAME";
		$sql .= "        ,M03_ENSEN_CD";
		$sql .= "        ,M03_EKI_CD";
		$sql .= "        ,M03_EKI_NAME";
		$sql .= "        ,A06_BUKKEN_CNT";
		$sql .= "        ,CEIL(DBMS_RANDOM.VALUE * 1000000) RANDOM";
		$sql .= "    FROM";
		$sql .= "        (";
		for( $i=1; $i<=3; $i++){
			$sql .= "            SELECT";
			$sql .= "                    COUNT(T03_HEYA_CD) AS CNT";
			$sql .= "                    ,M05_TENPO_CD";
			$sql .= "                    ,M05_TENPO_MAIL";
			$sql .= "                    ,M05_TENPO_NAME";
			$sql .= "                    ,M05_TENPO_TODOUFUKEN_CD";
			$sql .= "                    ,M05_TENPO_SHIKUCHOUSON_CD";
			$sql .= "                    ,M05_JYUUSHO_TEXT";
			$sql .= "                    ,M05_TENPO_BANCHI";
			$sql .= "                    ,M05_TENPO_JYUUSHO_BIKOU";
			$sql .= "                    ,M05_KOUTSUU_TEXT01";
			$sql .= "                    ,M05_TOHO01";
			$sql .= "                    ,M05_EIGYOU_ZIKAN_OPEN_H";
			$sql .= "                    ,M05_EIGYOU_ZIKAN_OPEN_M";
			$sql .= "                    ,M05_EIGYOU_ZIKAN_CLOSE_H";
			$sql .= "                    ,M05_EIGYOU_ZIKAN_CLOSE_M";
			$sql .= "                    ,M05_TEIKYUUBI";
			$sql .= "                    ,M03_ENSEN_NAME";
			$sql .= "                    ,M03_ENSEN_CD";
			$sql .= "                    ,M03_EKI_CD";
			$sql .= "                    ,M03_EKI_NAME";
			$sql .= "                    ,A06_BUKKEN_CNT";
			$sql .= "                FROM";
			$sql .= "                    T03WEB_HEYA";
			$sql .= "                    ,M05TENPO_INFO";
			$sql .= "                    ,M03EKI";
			$sql .= "                    ,A06TENPO_BUKKEN_COUNT";
			$sql .= "                    ,(";
			$sql .= "                        SELECT";
			$sql .= "                                T63_RENRAKU_CD AS TGT_RENRAKU_CD";
			$sql .= "                            FROM";
			$sql .= "                                T63RENRAKU_EKI_SUM";
			$sql .= "                            WHERE";
			$sql .=                                  $this->createWhere("T63_EKI_CD",$params['StCd']);
			$sql .= "                                AND T63_DATA_KUBUN = 0";
			$sql .= "                            GROUP BY";
			$sql .= "                                T63_RENRAKU_CD";
			$sql .= "                    )";
			$sql .= "                WHERE";
			$sql .= "                    T03_RENRAKU_EKI_CD0".$i." = TGT_RENRAKU_CD";
			$sql .= "                    AND T03_RENRAKU_EKI_CD0".$i." IS NOT NULL";
			$sql .= "                    AND M05_TENPO_CD = T03_TENPO_CD";
			$sql .= "                    AND M05_KOUKAI_FLAG = 1";
			$sql .= "                    AND M05_EKI_CD01 = M03_EKI_CD(+)";
			//$sql .= "                    AND M05_TENPO_CD <> '03008604'";
			$sql .= "                    AND (";
			$sql .= "                        M05_TENPO_CD > '01000000'";
			$sql .= "                        AND M05_TENPO_CD < '48000000'";
			$sql .= "                    )";
			$sql .= $this->createWhereNotinShopList("M05_TENPO_CD", $db_params);
			$sql .= "                    AND M05_TENPO_CD = A06_TENPO_CD";
			$sql .= "                    AND ".$this->createWhere("T03_TODOUFUKEN_CD",$params['PrefCd']);
			if(! empty($data["area_tenpo"])){
				$sql .= "                AND ".$this->createWhereNotEq("M05_TENPO_CD",$this->arrayRemade($data["area_tenpo"],"M05_TENPO_CD"));
			}
			$sql .= "                GROUP BY";
			$sql .= "                    M05_TENPO_CD";
			$sql .= "                    ,M05_TENPO_MAIL";
			$sql .= "                    ,M05_TENPO_NAME";
			$sql .= "                    ,M05_TENPO_TODOUFUKEN_CD";
			$sql .= "                    ,M05_TENPO_SHIKUCHOUSON_CD";
			$sql .= "                    ,M05_JYUUSHO_TEXT";
			$sql .= "                    ,M05_TENPO_BANCHI";
			$sql .= "                    ,M05_TENPO_JYUUSHO_BIKOU";
			$sql .= "                    ,M05_KOUTSUU_TEXT01";
			$sql .= "                    ,M05_TOHO01";
			$sql .= "                    ,M05_EIGYOU_ZIKAN_OPEN_H";
			$sql .= "                    ,M05_EIGYOU_ZIKAN_OPEN_M";
			$sql .= "                    ,M05_EIGYOU_ZIKAN_CLOSE_H";
			$sql .= "                    ,M05_EIGYOU_ZIKAN_CLOSE_M";
			$sql .= "                    ,M05_TEIKYUUBI";
			$sql .= "                    ,M03_ENSEN_NAME";
			$sql .= "                    ,M03_ENSEN_CD";
			$sql .= "                    ,M03_EKI_CD";
			$sql .= "                    ,M03_EKI_NAME";
			$sql .= "                    ,A06_BUKKEN_CNT";
			$sql .= "            UNION ALL";
			$sql .= "            SELECT";
			$sql .= "                    COUNT(T03_HEYA_CD) AS CNT";
			$sql .= "                    ,M05_TENPO_CD";
			$sql .= "                    ,M05_TENPO_MAIL";
			$sql .= "                    ,M05_TENPO_NAME";
			$sql .= "                    ,M05_TENPO_TODOUFUKEN_CD";
			$sql .= "                    ,M05_TENPO_SHIKUCHOUSON_CD";
			$sql .= "                    ,M05_JYUUSHO_TEXT";
			$sql .= "                    ,M05_TENPO_BANCHI";
			$sql .= "                    ,M05_TENPO_JYUUSHO_BIKOU";
			$sql .= "                    ,M05_KOUTSUU_TEXT01";
			$sql .= "                    ,M05_TOHO01";
			$sql .= "                    ,M05_EIGYOU_ZIKAN_OPEN_H";
			$sql .= "                    ,M05_EIGYOU_ZIKAN_OPEN_M";
			$sql .= "                    ,M05_EIGYOU_ZIKAN_CLOSE_H";
			$sql .= "                    ,M05_EIGYOU_ZIKAN_CLOSE_M";
			$sql .= "                    ,M05_TEIKYUUBI";
			$sql .= "                    ,M03_ENSEN_NAME";
			$sql .= "                    ,M03_ENSEN_CD";
			$sql .= "                    ,M03_EKI_CD";
			$sql .= "                    ,M03_EKI_NAME";
			$sql .= "                    ,A06_BUKKEN_CNT";
			$sql .= "                FROM";
			$sql .= "                    M05TENPO_INFO";
			$sql .= "                    ,T03WEB_HEYA";
			$sql .= "                    ,M03EKI";
			$sql .= "                    ,A06TENPO_BUKKEN_COUNT";
			$sql .= "                WHERE";
			$sql .=                      $this->createWhereTwoParam("T03_TODOUFUKEN_CD","T03_EKI_CD0".$i,$params['PrefCd'],$params['StCd']);
			if(! empty($data["area_tenpo"])){
				$sql .= "                AND ".$this->createWhereNotEq("M05_TENPO_CD",$this->arrayRemade($data["area_tenpo"],"M05_TENPO_CD"));
			}
			$sql .= "                    AND T03_RENRAKU_EKI_CD0".$i." IS NULL";
			$sql .= "                    AND M05_TENPO_CD = T03_TENPO_CD";
			$sql .= "                    AND M05_KOUKAI_FLAG = 1";
			$sql .= "                    AND M05_EKI_CD01 = M03_EKI_CD(+)";
			//$sql .= "                    AND M05_TENPO_CD <> '03008604'";
			$sql .= "                    AND (";
			$sql .= "                        M05_TENPO_CD > '01000000'";
			$sql .= "                        AND M05_TENPO_CD < '48000000'";
			$sql .= "                    )";
			$sql .= $this->createWhereNotinShopList("M05_TENPO_CD", $db_params);
			$sql .= "                     AND M05_TENPO_CD = A06_TENPO_CD";
			$sql .= "                GROUP BY";
			$sql .= "                    M05_TENPO_CD";
			$sql .= "                    ,M05_TENPO_MAIL";
			$sql .= "                    ,M05_TENPO_NAME";
			$sql .= "                    ,M05_TENPO_TODOUFUKEN_CD";
			$sql .= "                    ,M05_TENPO_SHIKUCHOUSON_CD";
			$sql .= "                    ,M05_JYUUSHO_TEXT";
			$sql .= "                    ,M05_TENPO_BANCHI";
			$sql .= "                    ,M05_TENPO_JYUUSHO_BIKOU";
			$sql .= "                    ,M05_KOUTSUU_TEXT01";
			$sql .= "                    ,M05_TOHO01";
			$sql .= "                    ,M05_EIGYOU_ZIKAN_OPEN_H";
			$sql .= "                    ,M05_EIGYOU_ZIKAN_OPEN_M";
			$sql .= "                    ,M05_EIGYOU_ZIKAN_CLOSE_H";
			$sql .= "                    ,M05_EIGYOU_ZIKAN_CLOSE_M";
			$sql .= "                    ,M05_TEIKYUUBI";
			$sql .= "                    ,M03_ENSEN_NAME";
			$sql .= "                    ,M03_ENSEN_CD";
			$sql .= "                    ,M03_EKI_CD";
			$sql .= "                    ,M03_EKI_NAME";
			$sql .= "                    ,A06_BUKKEN_CNT";
			if($i <> 3){
				$sql .= "            UNION ALL";
			}
		}
		$sql .= "        )";
		$sql .= "    GROUP BY";
		$sql .= "        M05_TENPO_CD";
		$sql .= "        ,M05_TENPO_MAIL";
		$sql .= "        ,M05_TENPO_NAME";
		$sql .= "        ,M05_TENPO_TODOUFUKEN_CD";
		$sql .= "        ,M05_TENPO_SHIKUCHOUSON_CD";
		$sql .= "        ,M05_JYUUSHO_TEXT";
		$sql .= "        ,M05_TENPO_BANCHI";
		$sql .= "        ,M05_TENPO_JYUUSHO_BIKOU";
		$sql .= "        ,M05_KOUTSUU_TEXT01";
		$sql .= "        ,M05_TOHO01";
		$sql .= "        ,M05_EIGYOU_ZIKAN_OPEN_H";
		$sql .= "        ,M05_EIGYOU_ZIKAN_OPEN_M";
		$sql .= "        ,M05_EIGYOU_ZIKAN_CLOSE_H";
		$sql .= "        ,M05_EIGYOU_ZIKAN_CLOSE_M";
		$sql .= "        ,M05_TEIKYUUBI";
		$sql .= "        ,M03_ENSEN_NAME";
		$sql .= "        ,M03_ENSEN_CD";
		$sql .= "        ,M03_EKI_CD";
		$sql .= "        ,M03_EKI_NAME";
		$sql .= "        ,A06_BUKKEN_CNT";
		$sql .= "    ORDER BY";
		$sql .= "        RANDOM";

		return $sql;
	}

	/**
	 *  ダミー店舗除外文作成
	 */
	private function createWhereNotinShopList($column, &$db_params)
	{
		$dummyShopList = array_filter(Apm_Config_Shop::$DUMMY_SHOP_LIST);
		if (empty($column) || empty($dummyShopList)) return '';
		
		$sql = "";
		$i = 0;
		foreach ($dummyShopList as $shopCd) {
			// in内部
			$i++;
			if ($i > 1) $sql .= ", ";
			
			$sql .= ":dummyShopCd_{$i} ";
			$db_params['dummyShopCd_'.$i] = $shopCd;
		}
		if (!empty($sql)) {
			// in外部
			$sql = " AND " . $column . " not in ( " . $sql . ") ";
		}
		
		return $sql;
	}

	/**
	 * 店舗名（駅名つき）リストの取得
	 *
	 * @param String $strSelShop 店舗コード文字列
	 * @return エラーコード
	 */
	public function getTenpoEkiNameList($strSelShop)
	{
		if (empty($strSelShop)) return array();
		
		//SQL作成
		$db_params = array();
		
		$sql = "SELECT DISTINCT M05_TENPO_CD, M05_TENPO_MAIL, M05_TENPO_NAME,";
		$sql .= " M05_HOUZINMEI, M05_TENPO_TODOUFUKEN_CD, M05_TENPO_SHIKUCHOUSON_CD,";
		$sql .= " M05_JYUUSHO_TEXT, M05_TENPO_BANCHI, M05_TENPO_JYUUSHO_BIKOU,";
		$sql .= " M05_KOUTSUU_TEXT01, M05_TOHO01, M05_EIGYOU_ZIKAN_OPEN_M,";
		$sql .= " M05_EIGYOU_ZIKAN_CLOSE_H, M05_EIGYOU_ZIKAN_CLOSE_M, M05_EIGYOU_ZIKAN_OPEN_H,";
		$sql .= " M03_EKI_NAME, M05_TEIKYUUBI ";
		$sql .= "FROM M05TENPO_INFO, M03EKI ";
		
		//配列の要素数だけWHERE句を作成する
		$sql .= "WHERE ";
		$sql .= "( ";
		
		$i = 1;
		foreach($strSelShop as $value){
			if ($i > 1) {
				$sql .= " OR ";
			}
			$sql .= "M05_TENPO_CD = :shopCd_{$i} ";
			$db_params['shopCd_'.$i] = $value;
			$i++;
		}
		
		$sql .= ")";
		$sql .= " AND M05_EKI_CD01 = M03_EKI_CD(+) ";
		//$sql .= " AND M05_KOUKAI_FLAG = 1";
		//$sql .= " AND M05_TENPO_CD <> '03008604'";
		//$sql .= " AND (M05_TENPO_CD > '01000000' AND M05_TENPO_CD < '48000000')";
		$sql .= "ORDER BY M05_TENPO_CD ";

		$shopList = $this->dbGetList($sql, $db_params);

		// 表示コンバート
		$cnt = count($shopList);
		for ($i=0; $i<$cnt; $i++) {
			$shopList[$i]['SHOP_DTL_URL'] = Apm_Bukkenurl::generateUrlByParametersShopDetail($shopList[$i]);
		}

		return $shopList;
	}

}
?>